using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using SqlSugar;
using twistmachinev2.Repository;

namespace twistmachinev2
{
    public partial class FrmSqliteQuery : Form
    {
        private SqliteRepository _repository;
        private string _connectionString;
        private bool _isConnected = false;

        public FrmSqliteQuery()
        {
            InitializeComponent();
        }

        private void FrmSqliteQuery_Load(object sender, EventArgs e)
        {
            // 设置默认数据库路径
            string defaultDbPath = BaseData.OPCUADatabasePath;
            txtDbPath.Text = defaultDbPath;

            // 初始化状态
            UpdateStatus("就绪");
            btnRefreshTables.Enabled = false;
        }

        #region 数据库连接管理

        private void btnConnectDb_Click(object sender, EventArgs e)
        {
            try
            {
                ConnectToDatabase();
            }
            catch (Exception ex)
            {
                MessageBox.Show($"连接数据库失败: {ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                UpdateStatus($"连接失败: {ex.Message}");
            }
        }

        private void ConnectToDatabase()
        {
            string dbPath = txtDbPath.Text.Trim();
            if (string.IsNullOrEmpty(dbPath))
            {
                MessageBox.Show("请输入数据库路径", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            // 如果是相对路径，转换为绝对路径
            if (!Path.IsPathRooted(dbPath))
            {
                dbPath = Path.Combine(Application.StartupPath, dbPath);
            }

            // 确保目录存在
            string directory = Path.GetDirectoryName(dbPath);
            if (!Directory.Exists(directory))
            {
                Directory.CreateDirectory(directory);
            }

            _connectionString = $"Data Source={dbPath}";

            // 释放之前的连接
            _repository?.Dispose();

            // 初始化Repository
            _repository = new SqliteRepository(_connectionString);

            _isConnected = _repository.IsConnected;
            btnRefreshTables.Enabled = true;
            UpdateStatus($"已连接到数据库: {dbPath}");

            // 自动刷新表列表
            RefreshTables();
        }

        #endregion

        #region 表管理

        private void btnRefreshTables_Click(object sender, EventArgs e)
        {
            RefreshTables();
        }

        private void RefreshTables()
        {
            if (!_isConnected || _repository == null)
            {
                MessageBox.Show("请先连接数据库", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            try
            {
                listBoxTables.Items.Clear();

                // 获取所有表名
                var tables = _repository.GetTableInfoList();
                foreach (var table in tables)
                {
                    listBoxTables.Items.Add(table.Name);
                }

                UpdateStatus($"已刷新表列表，共 {tables.Count} 个表");
            }
            catch (Exception ex)
            {
                MessageBox.Show($"刷新表列表失败: {ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                UpdateStatus($"刷新失败: {ex.Message}");
            }
        }

        private void listBoxTables_DoubleClick(object sender, EventArgs e)
        {
            if (listBoxTables.SelectedItem != null)
            {
                string tableName = listBoxTables.SelectedItem.ToString();
                txtSqlQuery.Text = $"SELECT * FROM {tableName} LIMIT 100;";
            }
        }

        private void btnBrowseDb_Click(object sender, EventArgs e)
        {
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                txtDbPath.Text = openFileDialog1.FileName;
            }
        }

        private void btnClearQuery_Click(object sender, EventArgs e)
        {
            txtSqlQuery.Text = string.Empty;
        }

        private void btnSaveQuery_Click(object sender, EventArgs e)
        {
            if (!string.IsNullOrWhiteSpace(txtSqlQuery.Text))
            {
                if (saveFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    try
                    {
                        File.WriteAllText(saveFileDialog1.FileName, txtSqlQuery.Text);
                        UpdateStatus("查询已保存");
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show($"保存失败: {ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
            }
        }

        private void btnLoadQuery_Click(object sender, EventArgs e)
        {
            openFileDialog1.Filter = "SQL文件|*.sql|文本文件|*.txt|所有文件|*.*";
            openFileDialog1.Title = "加载SQL查询";
            
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    txtSqlQuery.Text = File.ReadAllText(openFileDialog1.FileName);
                    UpdateStatus("查询已加载");
                }
                catch (Exception ex)
                {
                    MessageBox.Show($"加载失败: {ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
            
            // 恢复原始过滤器
            openFileDialog1.Filter = "SQLite数据库文件|*.db;*.sqlite;*.sqlite3|所有文件|*.*";
            openFileDialog1.Title = "选择SQLite数据库文件";
        }

        private void txtSqlQuery_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.F5)
            {
                btnExecuteQuery_Click(sender, e);
                e.Handled = true;
            }
        }

        private void FrmSqliteQuery_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.F5)
            {
                btnExecuteQuery_Click(sender, e);
                e.Handled = true;
            }
        }

        #endregion

        #region SQL查询执行

        private void btnExecuteQuery_Click(object sender, EventArgs e)
        {
            ExecuteQuery();
        }

        private void ExecuteQuery()
        {
            if (_repository == null)
            {
                MessageBox.Show("请先连接数据库", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            if (string.IsNullOrWhiteSpace(txtSqlQuery.Text))
            {
                MessageBox.Show("请输入SQL查询语句", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            try
            {
                UpdateStatus("正在执行查询...");
                
                string sql = txtSqlQuery.Text.Trim();
                
                if (sql.ToUpper().StartsWith("SELECT"))
                {
                    // 查询操作
                    var result = _repository.ExecuteSql(sql);
                    dataGridViewResults.DataSource = result;
                    UpdateStatus($"查询完成，返回 {result.Rows.Count} 行数据");
                }
                else
                {
                    // 非查询操作（INSERT, UPDATE, DELETE等）
                    int affectedRows = _repository.ExecuteCommand(sql);
                    dataGridViewResults.DataSource = null;
                    UpdateStatus($"命令执行完成，影响 {affectedRows} 行");
                    
                    // 如果是修改操作，刷新表列表
                    if (sql.ToUpper().Contains("CREATE") || sql.ToUpper().Contains("DROP") || sql.ToUpper().Contains("ALTER"))
                    {
                        RefreshTables();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"执行查询时发生错误: {ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                UpdateStatus("查询执行失败");
            }
        }

        #endregion

        #region 公共方法

        /// <summary>
        /// 获取Repository实例
        /// </summary>
        /// <returns>SqliteRepository实例</returns>
        public SqliteRepository GetRepository()
        {
            if (!_isConnected || _repository == null)
            {
                throw new InvalidOperationException("数据库未连接");
            }
            return _repository;
        }

        #endregion

        #region 辅助方法

        private void UpdateStatus(string message)
        {
            lblStatus.Text = $"{DateTime.Now:HH:mm:ss} - {message}";
            Application.DoEvents();
        }

        /// <summary>
        /// 检查数据库连接状态
        /// </summary>
        /// <returns>连接状态</returns>
        public bool IsConnected => _isConnected && _repository != null;


        #endregion

        private void FrmSqliteQuery_FormClosing(object sender, FormClosingEventArgs e)
        {
            _repository?.Dispose();
            components?.Dispose();
        }
    }
}
